package com.edu.dao;

import com.edu.bean.News;
import com.edu.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NewsDao<etConnection> {
    /**
     * 查询所有新闻
     */
    public List<News> findAll() {
        String sql = "SELECT * FROM news ORDER BY news_id DESC";
        List<News> newsList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                newsList.add(convertToNews(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return newsList;
    }

    /**
     * 根据ID查询新闻
     */
    public News findById(Integer newsId) {
        String sql = "SELECT * FROM news WHERE news_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, newsId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                return convertToNews(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加新闻
     */
    public boolean add(News news) {
        String sql = "INSERT INTO news (title, content, category, image_url, is_carousel, status, create_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, NOW())";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, news.getTitle());
            stmt.setString(2, news.getContent());
            stmt.setString(3, news.getCategory());
            stmt.setString(4, news.getImageUrl());
            stmt.setInt(5, news.getIsCarousel());
            stmt.setInt(6, news.getStatus());

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 更新新闻
     */
    public boolean update(News news) {
        String sql = "UPDATE news SET title = ?, content = ?, category = ?, status = ?, " +
                "is_carousel = ?, update_time = NOW()";
        if (news.getImageUrl() != null) {
            sql += ", image_url = ?";
        }
        sql += " WHERE news_id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, news.getTitle());
            stmt.setString(2, news.getContent());
            stmt.setString(3, news.getCategory());
            stmt.setInt(4, news.getStatus());
            stmt.setInt(5, news.getIsCarousel());

            if (news.getImageUrl() != null) {
                stmt.setString(6, news.getImageUrl());
                stmt.setInt(7, news.getNewsId());
            } else {
                stmt.setInt(6, news.getNewsId());
            }

            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除新闻
     */
    public boolean delete(Integer newsId) {
        String sql = "DELETE FROM news WHERE news_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, newsId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 查询总记录数
     */
    public int findTotalCount(String title) {
        String sql = "SELECT COUNT(*) FROM news";
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql += " WHERE title LIKE ?";
            params.add("%" + title.trim() + "%");
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 分页查询新闻
     */
    public List<News> findByPage(String title, int start, int pageSize) {
        String sql = "SELECT * FROM news";
        List<Object> params = new ArrayList<>();

        if (title != null && !title.trim().isEmpty()) {
            sql += " WHERE title LIKE ?";
            params.add("%" + title.trim() + "%");
        }

        sql += " ORDER BY news_id DESC LIMIT ?, ?";
        params.add(start);
        params.add(pageSize);

        List<News> newsList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                newsList.add(convertToNews(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return newsList;
    }

    /**
     * 查询轮播图新闻
     */
    public List<News> findCarouselNews() {
        String sql = "SELECT * FROM news WHERE is_carousel = 1 AND status = 1 ORDER BY news_id DESC";
        List<News> newsList = new ArrayList<>();

        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                newsList.add(convertToNews(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return newsList;
    }

    /**
     * 增加新闻浏览次数
     */
    public boolean incrementViewCount(Integer newsId) {
        String sql = "UPDATE news SET view_count = view_count + 1 WHERE news_id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, newsId);
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据分类分页查询新闻
     */
    public List<News> findByCategory(String category, int start, int pageSize) {
        String sql = "SELECT * FROM news WHERE status = 1";
        List<Object> params = new ArrayList<>();

        if (category != null && !category.trim().isEmpty()) {
            sql += " AND category = ?";
            params.add(category.trim());
        }

        sql += " ORDER BY news_id DESC LIMIT ?, ?";
        params.add(start);
        params.add(pageSize);

        List<News> newsList = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                newsList.add(convertToNews(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return newsList;
    }

    /**
     * 根据分类查询新闻总数
     */
    public int findTotalCountByCategory(String category) {
        String sql = "SELECT COUNT(*) FROM news WHERE status = 1";
        List<Object> params = new ArrayList<>();

        if (category != null && !category.trim().isEmpty()) {
            sql += " AND category = ?";
            params.add(category.trim());
        }

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 将ResultSet转换为News对象
     */
    private News convertToNews(ResultSet rs) throws SQLException {
        News news = new News();
        news.setNewsId(rs.getInt("news_id"));
        news.setTitle(rs.getString("title"));
        news.setContent(rs.getString("content"));
        news.setCategory(rs.getString("category"));
        news.setImageUrl(rs.getString("image_url"));
        news.setIsCarousel(rs.getInt("is_carousel"));
        news.setViewCount(rs.getInt("view_count"));
        news.setStatus(rs.getInt("status"));
        news.setCreateTime(rs.getTimestamp("create_time"));
        news.setUpdateTime(rs.getTimestamp("update_time"));
        return news;
    }
} 